package com.example.spsaveqqty.guides;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.spsaveqqty.travelguidesdatabase.DatabaseHelper;
import com.example.spsaveqqty.guides.Guide;
import java.util.ArrayList;

public class GuideDao {
    private DatabaseHelper mHelper;
    private SQLiteDatabase db = null,wb=null;
    private int place_id = 0,producer_id = 0;
    public GuideDao(Context context) {
        mHelper = new DatabaseHelper(context);
    }

    //查找用户输入的景区名是否存在
    public boolean findPlace(String placeName){
        try {
            db = mHelper.getReadableDatabase();
            Cursor cursor;
            cursor = db.rawQuery("select attrac_id from attraction where attrac_name=?", new String[]{placeName});
            //获取发布者id
            if (cursor.getCount() == 0) {
                cursor.close();
                return false;
            } else {
                cursor.close();
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != db) {
                db.close();
            }
        }
        return true;
    }

    public String insertPics(String[] pics){
        //插入图片到图片表
        String str = new String();
        try {
            wb = mHelper.getWritableDatabase();
            for (int i = 0; i < pics.length; i++) {
                wb.execSQL("insert into picture(url,status) values(?,?)", new Object[]{pics[i],1});
                String sql = "select last_insert_rowid() from picture" ;
                Cursor cursor = wb.rawQuery(sql, null);
                int a = -1;
                if(cursor.moveToFirst()){
                    a = cursor.getInt(0);
                    cursor.close();
                    if(i !=(pics.length+1))
                        str += Integer.toString(a)+",";
                    else
                        str += Integer.toString(a);
                }
                else
                    cursor.close();
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                //wb.close();
            }
        }
        return str;
    }

    public void insert(Guide guide) {
        try {
            db = mHelper.getReadableDatabase();
            Cursor cursor;
            cursor = db.rawQuery("select id from user where username=?", new String[]{guide.getProducer_name()});
            //获取发布者id
            if (null != cursor) {
                while (cursor.moveToNext()) {
                    producer_id = cursor.getInt(cursor.getColumnIndex("id"));
                }
            } else {
                cursor.close();
            }
            //获取景区id
            Cursor cur= db.rawQuery("select attrac_id from attraction where attrac_name=?", new String[]{guide.getPlace_name()});
            if (null != cur) {
                while (cur.moveToNext()) {
                    place_id = cur.getInt(cur.getColumnIndex("attrac_id"));
                }
            } else {
                cur.close();
            }

            String pics = new String();
            pics = insertPics(guide.getPicture());//插入图片
            String sql = "insert into guide(producer_id,title,place_id,datetime,conent,picture_id)" +
                    " values(?,?,?,?,?,?)";
            db.execSQL(sql, new Object[]{producer_id,guide.getTitle(),place_id,guide.getDatetime(),guide.getConent(),pics});
            db.close();
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != db) {
                db.close();
            }
        }
    }
    //查询所有景区的旅游攻略或某景区的所有攻略
    public ArrayList<Guide> getGuides(String name) {
        ArrayList<Guide> guidesList = new ArrayList<Guide>();
        try {
            db = mHelper.getReadableDatabase();
            Cursor cursor;
            if(name.equals("all")){
                cursor = db.rawQuery("select guide.*,attraction.attrac_name,user.picture AS image,user.flag,user.username from guide,attraction,user " +
                        "where guide.place_id = attraction.attrac_id and guide.producer_id = user.id",null);
            }
            else {
                cursor = db.rawQuery("select guide.*,attraction.attrac_name,user.picture AS image,user.flag,user.username from guide,attraction,user " +
                        "where guide.place_id = attraction.attrac_id and guide.producer_id = user.id" +
                        " and attraction.attrac_name = ?",new String[]{name});
            }
            if (null != cursor) {
                while (cursor.moveToNext()) {
                    Guide guide = new Guide();//定义攻略对象
                    guide.setStrategy_id(cursor.getInt(cursor.getColumnIndex("strategy_id")));
                    guide.setProducer_name(cursor.getString(cursor.getColumnIndex("username")));
                    guide.setIconFlag(cursor.getString(cursor.getColumnIndex("flag")));
                    guide.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                    guide.setConent(cursor.getString(cursor.getColumnIndex("conent")));
                    guide.setDatetime(cursor.getString(cursor.getColumnIndex("datetime")));
                    guide.setPlace_name(cursor.getString(cursor.getColumnIndex("attrac_name")));
                    guide.setProducer_image(cursor.getString(cursor.getColumnIndex("image")));//获取发布者头像地址
                    String pic = cursor.getString(cursor.getColumnIndex("picture_id"));
                    String[] picID = pic.split(",");
                    //获取图片表，攻略图片地址数组
                    int i;
                    String[] picPath = new String[4];
                    for (i=0;i<picID.length;i++) {
                        Cursor cur2 = db.rawQuery("select * from picture where id=?",new String[]{picID[i]});
                        if (null != cur2) {
                            while (cur2.moveToNext()) {
                                picPath[i] = cur2.getString(cur2.getColumnIndex("url"));
                            }
                        }
                        else
                            cur2.close();
                    }
                    guide.setPicture(picPath);
                    guidesList.add(guide);
                }
            }else{
                guidesList = null;
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != db) {
                db.close();
            }
        }
        return guidesList;

    }

}
